package com.hitrust.gs.net;

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Map;
import java.util.Iterator;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.hitrust.gs.lib.GSLogWriter;
import com.hitrust.gs.lib.SQLLogger;
import com.hitrust.gs.lib.db.GSBaseDAO;
import com.hitrust.gs.lib.db.GSPreparedStatementFactory;

import com.hitrust.gs.integration.GSApepInfoProcess;



public class GWBillServlet extends HttpServlet {
	
	public void init(ServletConfig aConfig) throws ServletException 
	{
		
	}
	
	private Connection iConn ;
	private PreparedStatement iPstmt;
	private ResultSet iRs = null;
	
	/**
	 * Logger for this class
	 */
	private static final Logger logger = Logger.getLogger("socket");
	
		
	public void doGet(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException 	
	{
		
	    doPost(request,response);
		
	}
	
	public void doPost(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException 	
	{
	    
	    
		try {
		    String sendCode = (String)request.getParameter("sendCode");   
		    if (sendCode == null) sendCode = "";
		    
		    String iSql = "";
		    String parms = "";
		    if (sendCode.equals(""))
            { 
		        parms = "--1";
            }
		    else
		    {
		        parms = sendCode; 
		    }
		    
            HSSFWorkbook tWb = new HSSFWorkbook();
            HSSFFont font = tWb.createFont();
            font.setFontName("宋体");
            HSSFCellStyle cellStyle= tWb.createCellStyle();
            cellStyle.setFont(font);  
            HSSFSheet tSheetHead = tWb.createSheet("GS_ORDER");

            iSql = "select a.* from gs_order a,gs_order_detail b where a.GS_ORDER_TEMPID=b.GS_ORDER_TEMPID ";
            iSql += " and b.GS_DELIVER_GOODS_ID = ?";
            CreateSheet(tSheetHead,iSql,new String[]{parms},cellStyle);
               
            HSSFSheet tSheetSub = tWb.createSheet("GS_ORDER_DETAIL"); 
            iSql = "select * from gs_order_detail where GS_DELIVER_GOODS_ID = ?";            
            CreateSheet(tSheetSub,iSql,new String[]{parms},cellStyle);

            response.setContentType("application/ms-excel;charset=GBK");
            response.addHeader("Content-disposition", "filename=SendBillData");
            ServletOutputStream out = response.getOutputStream();
            tWb.write(out);
            out.flush();
            out.close();
			
		} catch (Exception e) {
			GSLogWriter.error(logger,e.getLocalizedMessage(),"GWBillServlet.doPost执行失败");
		}		
	}	
	
	public void CreateSheet(HSSFSheet sheet,String sql,String[] parms,HSSFCellStyle cellStyle) throws Exception
	{
	    try {	
    	    iConn = new GSBaseDAO().getConnetion();
            iPstmt = iConn.prepareStatement(sql);
            GSPreparedStatementFactory.buildStatement(iPstmt, parms);
    
            // 执行
            iRs = iPstmt.executeQuery();
            ResultSetMetaData fields = iRs.getMetaData();   
            int row=0;
            HSSFRow tRow = sheet.createRow((short)row++);
            for(int i=1;i<=fields.getColumnCount();i++)
            {
                HSSFCell cell = tRow.createCell((short)(i-1));
                cell.setEncoding(HSSFCell.ENCODING_UTF_16);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(fields.getColumnName(i));
                cell.setCellStyle(cellStyle); 
            }
            while(iRs.next())
            {
                HSSFRow tRow1 = sheet.createRow((short)row++);
                for(int i=1;i<=fields.getColumnCount();i++)
                {
                    HSSFCell cell = tRow1.createCell((short)(i-1));
                    cell.setEncoding(HSSFCell.ENCODING_UTF_16);
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    cell.setCellValue(iRs.getString(i));  
                    cell.setCellStyle(cellStyle); 
                }
            }
            
            iRs.close();
            iPstmt.close();
            iRs = null;
            iPstmt = null;
        } catch (SQLException e) {
            SQLLogger.log("error:"+e.toString());
            GSLogWriter.debug(this, e);
            GSLogWriter.debug(this, sql);
            throw e;
        }finally{
            close();
        }
	}
	
    public void close() {
        if (iRs != null) {
            try {
                iRs.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        if (iPstmt != null) {
            try {
                iPstmt.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }

        try {
            if (iConn != null && !iConn.isClosed()) {
                iConn.close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

    }

}
